﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTAB05DOCData : BaseFunction
    {

        public JLTAB05DOCData()
            : base()
        {

        }
        public JLTAB05DOCData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetABPOS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABPOS";
            string KRS = dwABPOS["KRS"].ToString().Trim();
            string LFD = dwABPOS["LFD"].ToString().Trim();
            string POS = dwABPOS["POS"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select ABPAUFKRS,ABPAUFLFD,ABPAUFPOS,ABPART,ABPVAR,ABPFARBE, "
                    + " ABPBESCHICH,ABPSOLLMENGE1*0.01 as QTY,ABPSTARTDATA,ABPENDDATA,ABPANRKR,ABPALFDNR,TRIM(B.APKSPEZIF) AS BEZ "
                    + " from abpos A,APKOPF B "
                    + " where abpaufkrs = '" + KRS + "' AND ABPAUFLFD = '" + LFD + "' "
                    + " AND A.ABPMANDANT = B.APKMANDANT AND A.ABPANRKR = B.APKANRKRS AND A.ABPALFDNR = B.APKALFDNR "
                    + " ORDER BY ABPAUFPOS "
                    ;
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEZ"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetABFOLGE(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABFOLGE";
            string KRS = dwABPOS["ABPAUFKRS"].ToString().Trim();
            string LFD = dwABPOS["ABPAUFLFD"].ToString().Trim();
            string POS = dwABPOS["ABPAUFPOS"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select ABFAUFKRS,ABFAUFLFD,ABFAUFPOS,ABFAFOLGE,ABFAGANG, "
                    + " TRIM(ABFBZAGANG)AS STEP,ABFMTYP,ABFSOLLSTART,ABFSOLLDAT  "
                    + " from ABFOLGE "
                    + " WHERE ABFAUFKRS = '" + KRS + "' AND ABFAUFLFD = '" + LFD + "' "
                    + " AND ABFAUFPOS = '" + POS + "' AND ABFMANUELKZ = 'N' "
                    + " ORDER BY ABFAFOLGE "
                    ;
                Result = Connect2Proxy(orlReadCommand);
                Result.Tables[0].TableName = TableName;
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }

        public DataSet GetABE(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABE";
            string KRS = dwABPOS["ABPAUFKRS"].ToString().Trim();
            string LFD = dwABPOS["ABPAUFLFD"].ToString().Trim();
            string POS = dwABPOS["ABPAUFPOS"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT ABEAUFKRS,ABEAUFLFD,ABEAUFPOS,ABEAFOLGE,TRIM(ABFBZAGANG)AS STEP, "
                    + " ABFKSTKRS,ABFKST,ABFMTYP,ABEMODNR,ABEEINSTNR,TRIM(AMEBEZ) AS AMEBEZ,NVL(TRIM(ABEIWERTX),ABEWERTX) AS ABEWERTX, "
                    + " NVL(TRIM(ABEWERTN),ABEWERTN) AS ABEWERTN,AMEMASS "
                    + " FROM ABARTE A,ABFOLGE B,AMEINST C,AMSTAMM D "
                    + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS "
                    + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS "
                    + " AND A.ABEAFOLGE = B.ABFAFOLGE AND A.ABEMANDANT = C.AMEMANDANT "
                    + " AND A.ABEMODNR = C.AMEMODNR AND A.ABEEINSTNR = C.AMEEINSTNR "
                    + " AND B.ABFMANDANT = D.AMMANDANT  AND B.ABFKSTKRS = D.AMKSTKRS "
                    + " AND B.ABFKST = D.AMKST AND B.ABFMTYP = D.AMTYP "
                    + " AND D.AMMANDANT = C.AMEMANDANT AND D.AMNRKRS = C.AMENRKRS "
                    + " AND D.AMLFDNR = C.AMELFDNR AND D.AMPOS = C.AMEPOS "
                    + " AND ABEAEVOR= '0001' "
                    + " AND abeaufkrs = '" + KRS + "' AND abeauflfd = '" + LFD + "' "
                    + " AND ABEAUFPOS = '" + POS + "' "
                    + " ORDER BY ABEAFOLGE,ABEMODNR,ABEEINSTNR  "
                    ;
                Result = Connect2Proxy(orlReadCommand);
                 
                    Result.Tables[0].TableName = TableName;
                     
             
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public DataSet GetABARTE(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABARTE";
            string KRS = dwABPOS["ABPAUFKRS"].ToString().Trim();
            string LFD = dwABPOS["ABPAUFLFD"].ToString().Trim();
            string POS = dwABPOS["ABPAUFPOS"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT ABEAUFKRS,ABEAUFLFD,ABEAUFPOS,ABEAFOLGE,TRIM(ABFBZAGANG)AS STEP, "
                    + " ABFKSTKRS,ABFKST,ABFMTYP,ABEMODNR,ABEEINSTNR,TRIM(AMEBEZ) AS AMEBEZ,ABEWERTX,ABEWERTN,AMEMASS "
                    + " FROM ABARTE A,ABFOLGE B,AMEINST C,AMSTAMM D "
                    + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS "
                    + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS "
                    + " AND A.ABEAFOLGE = B.ABFAFOLGE AND A.ABEMANDANT = C.AMEMANDANT "
                    + " AND A.ABEMODNR = C.AMEMODNR AND A.ABEEINSTNR = C.AMEEINSTNR "
                    + " AND B.ABFMANDANT = D.AMMANDANT  AND B.ABFKSTKRS = D.AMKSTKRS "
                    + " AND B.ABFKST = D.AMKST AND B.ABFMTYP = D.AMTYP "
                    + " AND D.AMMANDANT = C.AMEMANDANT AND D.AMNRKRS = C.AMENRKRS "
                    + " AND D.AMLFDNR = C.AMELFDNR AND D.AMPOS = C.AMEPOS "
                    + " AND ABEAEVOR= '0001' "
                    + " AND abeaufkrs = '" + KRS + "' AND abeauflfd = '" + LFD + "' "
                    + " AND ABEAUFPOS = '" + POS + "' "
                    + " ORDER BY ABEAFOLGE,ABEMODNR,ABEEINSTNR  "
                    ;
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName; 
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public void COPY(DataSet setREPORT)
        {
            DataRow dwABARTE;
            DataRow dwABE;

            try
            {
                dwABARTE = setREPORT.Tables["ABARTE"].Rows[0];
                dwABE = setREPORT.Tables["ABE"].Rows[0];
                string KRS = dwABE["ABEAUFKRS"].ToString().Trim();
                string LFD = dwABE["ABEAUFLFD"].ToString().Trim();
                string POS = dwABE["ABEAUFPOS"].ToString().Trim();
                string FKRS = dwABARTE["ABEAUFKRS"].ToString().Trim();
                string FLFD = dwABARTE["ABEAUFLFD"].ToString().Trim();
                OracleCommand or1;
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE ABARTE T "
                    + " SET (T.ABEWERTX,T.ABEWERTN) =( "
                    + " SELECT MAX(NVL(TRIM(A.ABEIWERTX),A.ABEWERTX)) AS ABEWERTX, "
                    + " MAX(NVL(TRIM(A.ABEWERTN),A.ABEWERTN)) AS ABEWERTN "
                    + " FROM ABARTE A,ABFOLGE B,ABFOLGE X "
                    + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS "
                    + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS  "
                    + " AND A.ABEAFOLGE = B.ABFAFOLGE  "
                    + " AND A.ABEAEVOR= '0001'  "
                    + " AND A.abeaufkrs = '" + KRS + "' AND A.abeauflfd = '" + LFD + "'  "
                    + " AND A.ABEAUFPOS = '" + POS + "' "
                    + " AND A.ABEMANDANT = T.ABEMANDANT AND A.ABEMODNR = T.ABEMODNR AND A.ABEEINSTNR = T.ABEEINSTNR "
                    + " AND B.ABFKSTKRS = X.ABFKSTKRS AND B.ABFKST = X.ABFKST "
                    + " AND B.ABFMTYP = X.ABFMTYP AND T.ABEMANDANT = X.ABFMANDANT "
                    + " AND T.ABEAUFKRS = X.ABFAUFKRS AND T.ABEAUFLFD = X.ABFAUFLFD "
                    + " AND T.ABEAUFPOS = X.ABFAUFPOS AND T.ABEAFOLGE = X.ABFAFOLGE "
                    + " AND X.ABFAUFKRS = '" + FKRS + "' AND X.ABFAUFLFD = '" + FLFD + "') "
                    + " WHERE T.ABEAUFKRS = '" + FKRS + "' AND T.ABEAUFLFD = '" + FLFD + "'  "
                    + " AND T.ABEEINSTNR <>'000' ";

                Connect2Proxy(or1);

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void SAVE(DataSet setREPORT)
        {
            try
            {
                OracleCommand or1;
                or1 = OracleDB.CreateCommand();
                string KRS = "", LFD = "", POS = "", FOLGE = "", MODNR = "", EINSTNR = "";
                string ABEWERTX = "", ABEWERTN = "";
                foreach (DataRow dw in setREPORT.Tables["ABARTE"].Rows)
                {
                    KRS = dw["ABEAUFKRS"].ToString().Trim();
                    LFD = dw["ABEAUFLFD"].ToString().Trim();
                    POS = dw["ABEAUFPOS"].ToString().Trim();
                    FOLGE = dw["ABEAFOLGE"].ToString().Trim();
                    MODNR = dw["ABEMODNR"].ToString().Trim();
                    EINSTNR = dw["ABEEINSTNR"].ToString().Trim();
                    ABEWERTX = Left(dw["ABEWERTX"].ToString().Trim() + "                ", 16);
                    ABEWERTN = "0000000000000000";
                    or1.CommandText = "UPDATE ABARTE SET "
                        + " ABEWERTX = '" + ABEWERTX + "', "
                        + " ABEWERTN = '" + ABEWERTN + "' "
                        + " WHERE ABEAUFKRS = '" + KRS + "' "
                        + " AND ABEAUFLFD = '" + LFD + "' "
                        + " AND ABEAUFPOS = '" + POS + "' "
                        + " AND ABEAFOLGE = '" + FOLGE + "' "
                        + " AND ABEMODNR = '" + MODNR + "' "
                        + " AND ABEEINSTNR = '" + EINSTNR + "' "
                        ;

                    Connect2Proxy(or1);

                }

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public DataSet REPORT(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABPOS";
            string KRS = dwABPOS["KRS"].ToString().Trim();
            string LFD = dwABPOS["LFD"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT A.ABEAUFKRS,A.ABEAUFLFD,A.ABEAUFPOS,P.ABPART,P.ABPFARBE,P.ABPBESCHICH, "
                        + " TRIM(F.APKSPEZIF) AS BEZ,B.ABFAFOLGE,B.ABFAGANG,TRIM(B.ABFBZAGANG)AS STEP, "
                        + " A.ABEMODNR,ABEEINSTNR,TRIM(C.AMEBEZ) AS AMEBEZ,A.ABEWERTX,C.AMEMASS,'' AS DIFF, "
                        + " (select stragg(trim(abpfarbe)) from abpos t where t.abpmandant = p.abpmandant "
                        + " and t.abpaufkrs = p.abpaufkrs and t.abpauflfd = p.abpauflfd) as COLOR, "
                        + " (SELECT VAABRTBIS*0.01-1||'-'||VAABRTBIS*0.01 FROM VAART V "
                        + " WHERE V.VAAMANDANT = P.ABPMANDANT AND V.VAADART = P.ABPART) AS WIDTH, "
                        + " (select MAX(TRIM(T2.TXTKTEXT)) AS YARN "
                        + " from txtind T1,TXTKON T2 where T1.txtitxtart = 'VAD' "
                        + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                        + " AND TRIM(SUBSTR(T1.TXTISIND,6,20)) = TRIM(P.ABPART)) AS YARN, "
                        + " (SELECT (QPSNSWERT+QPSNSUG)*0.001||'~'||(QPSNSWERT+QPSNSOG)*0.001 "
                        + " FROM QPZUORD A,QPSTEP B "
                        + " WHERE A.QPZMANDANT = B.QPSMANDANT AND A.QPZPNRKRS = B.QPSPNRKRS "
                        + " AND A.QPZPLFDNR = B.QPSPLFDNR AND B.QPSMERKM = 'SS13' "
                        + " AND A.QPZARTST = 'F' AND QPZART = P.ABPART) AS SS "
                        + " FROM ABARTE A,ABFOLGE B,AMEINST C,AMSTAMM D,ABPOS P,APKOPF F "
                        + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS  "
                        + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS "
                        + " AND A.ABEAFOLGE = B.ABFAFOLGE AND A.ABEMANDANT = C.AMEMANDANT "
                        + " AND A.ABEMODNR = C.AMEMODNR AND A.ABEEINSTNR = C.AMEEINSTNR "
                        + " AND B.ABFMANDANT = D.AMMANDANT  AND B.ABFKSTKRS = D.AMKSTKRS "
                        + " AND B.ABFKST = D.AMKST AND B.ABFMTYP = D.AMTYP "
                        + " AND D.AMMANDANT = C.AMEMANDANT AND D.AMNRKRS = C.AMENRKRS "
                        + " AND D.AMLFDNR = C.AMELFDNR AND D.AMPOS = C.AMEPOS "
                        + " AND A.ABEMANDANT = P.ABPMANDANT AND A.ABEAUFKRS = P.ABPAUFKRS "
                        + " AND A.ABEAUFLFD = P.ABPAUFLFD AND A.ABEAUFPOS = P.ABPAUFPOS "
                        + " AND P.ABPMANDANT = F.APKMANDANT AND P.ABPANRKR = F.APKANRKRS  "
                        + " AND P.ABPALFDNR = F.APKALFDNR AND ABEAEVOR= '0001' "
                        + " AND abeaufkrs = '" + KRS + "' AND abeauflfd = '" + LFD + "' "
                        + " AND ABEAUFPOS = '001' "
                        + " ORDER BY ABFAFOLGE,ABEMODNR,ABEEINSTNR "
                    ;
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEZ"] = value; 
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["AMEBEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["AMEBEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["AMEMASS"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["AMEMASS"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["YARN"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["YARN"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ABEWERTX"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ABEWERTX"] = value;
                    }
                }
                DataSet set = new DataSet();
                foreach (DataRow row in Result.Tables[0].Rows)
                {
                    string STR = GetDIFF(row);
                    row["DIFF"] = STR;
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public string GetDIFF(DataRow dwABPOS)
        {
            string DIFF = "";

            string TableName = "DIFF";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select distinct stragg(diff) AS DIFF from ( "
                    + " SELECT distinct 'COLOR-'||TRIM(ABPFARBE)|| "
                    + " STRAGG(DISTINCT trim(ABFBZAGANG)||'-'||TRIM(AMEBEZ)||'/'||TRIM(ABEWERTX)) "
                    + " OVER(PARTITION BY ABPFARBE) diff "
                    + " FROM ABARTE A,ABFOLGE B,AMEINST C,AMSTAMM D,ABPOS P "
                    + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS  "
                    + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS "
                    + " AND A.ABEAFOLGE = B.ABFAFOLGE AND A.ABEMANDANT = C.AMEMANDANT "
                    + " AND A.ABEMODNR = C.AMEMODNR AND A.ABEEINSTNR = C.AMEEINSTNR "
                    + " AND B.ABFMANDANT = D.AMMANDANT  AND B.ABFKSTKRS = D.AMKSTKRS "
                    + " AND B.ABFKST = D.AMKST AND B.ABFMTYP = D.AMTYP "
                    + " AND D.AMMANDANT = C.AMEMANDANT AND D.AMNRKRS = C.AMENRKRS "
                    + " AND D.AMLFDNR = C.AMELFDNR AND D.AMPOS = C.AMEPOS "
                    + " AND A.ABEMANDANT = P.ABPMANDANT AND A.ABEAUFKRS = P.ABPAUFKRS "
                    + " AND A.ABEAUFLFD = P.ABPAUFLFD AND A.ABEAUFPOS = P.ABPAUFPOS "
                    + " AND ABEAEVOR= '0001' AND ABEAUFPOS <> '001' "
                    + " AND abeaufkrs = :ABEAUFKRS AND abeauflfd = :ABEAUFLFD "
                    + " AND EXISTS (SELECT '1' "
                    + " FROM ABARTE A1,ABFOLGE B1,AMEINST C1,AMSTAMM D1  "
                    + " where A1.ABEMANDANT = B1.ABFMANDANT AND A1.ABEAUFKRS = B1.ABFAUFKRS  "
                    + " AND A1.ABEAUFLFD = B1.ABFAUFLFD AND A1.ABEAUFPOS = B1.ABFAUFPOS "
                    + " AND A1.ABEAFOLGE = B1.ABFAFOLGE AND A1.ABEMANDANT = C1.AMEMANDANT "
                    + " AND A1.ABEMODNR = C1.AMEMODNR AND A1.ABEEINSTNR = C1.AMEEINSTNR "
                    + " AND B1.ABFMANDANT = D1.AMMANDANT  AND B1.ABFKSTKRS = D1.AMKSTKRS "
                    + " AND B1.ABFKST = D1.AMKST AND B1.ABFMTYP = D1.AMTYP "
                    + " AND D1.AMMANDANT = C1.AMEMANDANT AND D1.AMNRKRS = C1.AMENRKRS "
                    + " AND D1.AMLFDNR = C1.AMELFDNR AND D1.AMPOS = C1.AMEPOS "
                    + " AND A1.ABEAEVOR= '0001' AND A1.ABEAUFPOS = '001' "
                    + " AND A1.abeaufkrs = A.ABEAUFKRS AND A1.abeauflfd = A.ABEAUFLFD "
                    + " AND B1.ABFAGANG = B.ABFAGANG AND A1.ABEMODNR = A.ABEMODNR "
                    + " AND C1.AMEBEZ = C.AMEBEZ AND A1.ABEWERTX <> A.ABEWERTX )) "
                    ;
                orlReadCommand.Parameters.Add("ABEAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABEAUFKRS"].ToString().Trim();
                orlReadCommand.Parameters.Add("ABEAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABEAUFLFD"].ToString().Trim();

                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DIFF"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        DIFF = value;
                    }

                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return DIFF;
        }
        public DataSet MACHINE(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "ABPOS";
            string KRS = dwABPOS["KRS"].ToString().Trim();
            string LFD = dwABPOS["LFD"].ToString().Trim();
            string FOLGE = dwABPOS["FOLGE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT DISTINCT A.ABEAUFKRS,A.ABEAUFLFD,A.ABEAUFPOS,P.ABPART,P.ABPFARBE,P.ABPBESCHICH, "
                        + " TRIM(F.APKSPEZIF) AS BEZ,B.ABFAFOLGE,B.ABFAGANG,TRIM(B.ABFBZAGANG)AS STEP, "
                        + " A.ABEMODNR,ABEEINSTNR,TRIM(C.AMEBEZ) AS AMEBEZ,A.ABEWERTX,C.AMEMASS,'' AS DIFF, "
                        + " (select stragg(trim(abpfarbe)) from abpos t where t.abpmandant = p.abpmandant "
                        + " and t.abpaufkrs = p.abpaufkrs and t.abpauflfd = p.abpauflfd) as COLOR, "
                        + " (SELECT (QPSNSWERT+QPSNSUG)*0.001||'~'||(QPSNSWERT+QPSNSOG)*0.001 "
                        + " FROM QPZUORD A,QPSTEP B "
                        + " WHERE A.QPZMANDANT = B.QPSMANDANT AND A.QPZPNRKRS = B.QPSPNRKRS "
                        + " AND A.QPZPLFDNR = B.QPSPLFDNR AND B.QPSMERKM = 'SS13' "
                        + " AND A.QPZARTST = 'F' AND QPZART = P.ABPART) AS SS "
                        + " FROM ABARTE A,ABFOLGE B,AMEINST C,AMSTAMM D,ABPOS P,APKOPF F "
                        + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS  "
                        + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS "
                        + " AND A.ABEAFOLGE = B.ABFAFOLGE AND A.ABEMANDANT = C.AMEMANDANT "
                        + " AND A.ABEMODNR = C.AMEMODNR AND A.ABEEINSTNR = C.AMEEINSTNR "
                        + " AND B.ABFMANDANT = D.AMMANDANT  AND B.ABFKSTKRS = D.AMKSTKRS "
                        + " AND B.ABFKST = D.AMKST AND B.ABFMTYP = D.AMTYP "
                        + " AND D.AMMANDANT = C.AMEMANDANT AND D.AMNRKRS = C.AMENRKRS "
                        + " AND D.AMLFDNR = C.AMELFDNR AND D.AMPOS = C.AMEPOS "
                        + " AND A.ABEMANDANT = P.ABPMANDANT AND A.ABEAUFKRS = P.ABPAUFKRS "
                        + " AND A.ABEAUFLFD = P.ABPAUFLFD AND A.ABEAUFPOS = P.ABPAUFPOS "
                        + " AND P.ABPMANDANT = F.APKMANDANT AND P.ABPANRKR = F.APKANRKRS  "
                        + " AND P.ABPALFDNR = F.APKALFDNR AND ABEAEVOR= '0001' "
                        + " AND abeaufkrs = '" + KRS + "' AND abeauflfd = '" + LFD + "' "
                        + " AND B.ABFAFOLGE = '" + FOLGE + "' "
                        + " AND ABEAUFPOS = '001' "
                        + " ORDER BY ABFAFOLGE,ABEMODNR,ABEEINSTNR "
                    ;
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["BEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["BEZ"] = value; 
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["AMEBEZ"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["AMEBEZ"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["AMEMASS"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["AMEMASS"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["ABEWERTX"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["ABEWERTX"] = value;
                    }
                }
                DataSet set = new DataSet();
                foreach (DataRow row in Result.Tables[0].Rows)
                {
                    string STR = GetMCHINEDIFF(row);
                    row["DIFF"] = STR;
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }
        public string GetMCHINEDIFF(DataRow dwABPOS)
        {
            string DIFF = "";

            string TableName = "DIFF";
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "select distinct stragg(diff) AS DIFF from ( "
                    + " SELECT distinct 'COLOR-'||TRIM(ABPFARBE)|| "
                    + " STRAGG(DISTINCT trim(ABFBZAGANG)||'-'||TRIM(AMEBEZ)||'/'||TRIM(ABEWERTX)) "
                    + " OVER(PARTITION BY ABPFARBE) diff "
                    + " FROM ABARTE A,ABFOLGE B,AMEINST C,AMSTAMM D,ABPOS P "
                    + " where A.ABEMANDANT = B.ABFMANDANT AND A.ABEAUFKRS = B.ABFAUFKRS  "
                    + " AND A.ABEAUFLFD = B.ABFAUFLFD AND A.ABEAUFPOS = B.ABFAUFPOS "
                    + " AND A.ABEAFOLGE = B.ABFAFOLGE AND A.ABEMANDANT = C.AMEMANDANT "
                    + " AND A.ABEMODNR = C.AMEMODNR AND A.ABEEINSTNR = C.AMEEINSTNR "
                    + " AND B.ABFMANDANT = D.AMMANDANT  AND B.ABFKSTKRS = D.AMKSTKRS "
                    + " AND B.ABFKST = D.AMKST AND B.ABFMTYP = D.AMTYP "
                    + " AND D.AMMANDANT = C.AMEMANDANT AND D.AMNRKRS = C.AMENRKRS "
                    + " AND D.AMLFDNR = C.AMELFDNR AND D.AMPOS = C.AMEPOS "
                    + " AND A.ABEMANDANT = P.ABPMANDANT AND A.ABEAUFKRS = P.ABPAUFKRS "
                    + " AND A.ABEAUFLFD = P.ABPAUFLFD AND A.ABEAUFPOS = P.ABPAUFPOS "
                    + " AND ABEAEVOR= '0001' AND ABEAUFPOS <> '001' "
                    + " AND abeaufkrs = :ABEAUFKRS AND abeauflfd = :ABEAUFLFD and b.ABFAGANG = :ABFAGANG "
                    + " AND EXISTS (SELECT '1' "
                    + " FROM ABARTE A1,ABFOLGE B1,AMEINST C1,AMSTAMM D1  "
                    + " where A1.ABEMANDANT = B1.ABFMANDANT AND A1.ABEAUFKRS = B1.ABFAUFKRS  "
                    + " AND A1.ABEAUFLFD = B1.ABFAUFLFD AND A1.ABEAUFPOS = B1.ABFAUFPOS "
                    + " AND A1.ABEAFOLGE = B1.ABFAFOLGE AND A1.ABEMANDANT = C1.AMEMANDANT "
                    + " AND A1.ABEMODNR = C1.AMEMODNR AND A1.ABEEINSTNR = C1.AMEEINSTNR "
                    + " AND B1.ABFMANDANT = D1.AMMANDANT  AND B1.ABFKSTKRS = D1.AMKSTKRS "
                    + " AND B1.ABFKST = D1.AMKST AND B1.ABFMTYP = D1.AMTYP "
                    + " AND D1.AMMANDANT = C1.AMEMANDANT AND D1.AMNRKRS = C1.AMENRKRS "
                    + " AND D1.AMLFDNR = C1.AMELFDNR AND D1.AMPOS = C1.AMEPOS "
                    + " AND A1.ABEAEVOR= '0001' AND A1.ABEAUFPOS = '001' "
                    + " AND A1.abeaufkrs = A.ABEAUFKRS AND A1.abeauflfd = A.ABEAUFLFD "
                    + " AND B1.ABFAGANG = B.ABFAGANG AND A1.ABEMODNR = A.ABEMODNR "
                    + " AND C1.AMEBEZ = C.AMEBEZ AND A1.ABEWERTX <> A.ABEWERTX )) "
                    ;
                orlReadCommand.Parameters.Add("ABEAUFKRS", OracleType.NVarChar).Value = dwABPOS["ABEAUFKRS"].ToString();
                orlReadCommand.Parameters.Add("ABEAUFLFD", OracleType.NVarChar).Value = dwABPOS["ABEAUFLFD"].ToString();
                orlReadCommand.Parameters.Add("ABFAGANG", OracleType.NVarChar).Value = dwABPOS["ABFAGANG"].ToString();

                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in dsResult.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DIFF"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        DIFF = value;
                    }

                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return DIFF;
        }
    }
}